<?php
class M_dashboard extends Nvl_Model{
	function __construct(){
		parent::__construct();
		$this->table_name='m_dashboard';
		$this->column_primary='dashboard_id';
		$this->column_order='';
		$this->column_unique='dashboard_nama';
		$this->column_active='dashboard_aktif';
	}
	
	function get_trend(){
		$sql = "
			SELECT
				DATE_FORMAT(prestasi_tanggal, '%m-%Y') as bulan,
				COUNT(*) AS data
			FROM t_prestasi
			WHERE prestasi_tanggal < now()
			AND prestasi_tanggal > DATE_SUB(now(),INTERVAL 1 YEAR)
			GROUP BY bulan
		";
		$query = $this->db->query($sql);
		return json_encode($query->result());
	}
	
	function get_best_school(){
		$sql = "
			SELECT
				sekolah_nama,
				COUNT(*) AS data
			FROM t_prestasi
			JOIN m_sekolah ON m_sekolah.sekolah_id = t_prestasi.prestasi_sekolah_id
			GROUP BY sekolah_nama
			ORDER BY data  DESC
		";
		$query = $this->db->query($sql);
		return json_encode($query->result());
	}
	
	function get_list($start, $end){
		$this->db->select('sekolah_nama,
			sum(prestasi_skor) AS prestasi_skor,
			count(prestasi_id) AS prestasi_jumlah'
		);
		if($start != 0 && $end != 0){
			$this->db->limit($end, $start);
		}
		$this->db->join('m_sekolah', 'sekolah_id = prestasi_sekolah_id')->group_by('sekolah_id');
		$this->db->order_by('prestasi_jumlah', 'desc');
		$record = $this->db->get('t_prestasi');
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
	function get_list_sekolah_grid($start, $end){
		$this->db->select('siswa_nama, siswa_kelas,
			sum(prestasi_skor) AS prestasi_skor,
			count(prestasi_id) AS prestasi_jumlah'
		);
		if($start != 0 && $end != 0){
			$this->db->limit($end, $start);
		}
		$this->db->join('m_sekolah', 'sekolah_id = prestasi_sekolah_id')->group_by('sekolah_id');
		$this->db->join('m_siswa', 'sekolah_id = prestasi_sekolah_id')->group_by('sekolah_id');
		$record = $this->db->get('t_prestasi');
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
}